﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web.Mvc;
using GWEB.WebBase.Data;
using GWEB.WebBase.Entity;
using GWEB.WebBase.Utility;
using GWEB.WebFrame.Public;
using Newtonsoft.Json;

namespace GWEB.WebFrame.Schedule
{
    public class StudentClassController : PageInfoBase
    {
        [ActionFilter(IsLogin = true)]
        public ActionResult Index()
        {
            return View(BasePage.V("Schedule/StudentClass"));
        }
        //按天排课
        [ActionFilter(IsJson = true)]
        public JsonResult DayData(String date, int range)
        {
            date = Fun.cSql(date);
            Dictionary<String, Object> data = new Dictionary<String, Object>();
            DataRow drUser = BasePage.GetUser(Session);
            String leagueId = Fun.cString(drUser["LeagueId"]);
            String userId = Fun.cString(drUser["iUserId"]);

            //教室
            String sql = String.Format("select id,name from cu_ClassRoom where state=1 and LeagueId={0} order by id", leagueId);
            Dictionary<String, Object>[] header = BasePage.bd.ExecuteDictArray(sql);
            data.Add("header", header);
            //上课时段
            sql = String.Format("select id,startTime,endTime from cu_Time where state=1 and LeagueId={0} order by startTime,endTime", leagueId);
            Dictionary<String, Object>[] time = BasePage.bd.ExecuteDictArray(sql);
            data.Add("time", time);
            //教师排课
            String cond = "";
            if (range == 1) cond += String.Format(" and TeacherId={0} and TwoTeacherId={0}", userId);
            sql = String.Format(@"select 
    t1.id,'' keyCode, t1.ClassRoomId, t1.CourseName, t1.TimeId, t1.StudentNumber, t1.Color,t1.TeacherName,t1.TwoTeacherName,
	isnull(t2.Count,0) Count,isnull(t2.noSignCount,0) noSignCount,isnull(t2.signCount,0) signCount,isnull(t2.truancyCount,0) truancyCount,
isnull(t2.leaveCount,0) leaveCount,isnull(t2.waitCount,0) waitCount	
from cu_TeacherCourse t1
left
join (
select Tid, 
    sum(case when SignupClass = 0 and state !=4  then 1 else 0 end) Count,
	sum(case when SignupClass = 0 and state = 0 then 1 else 0 end) noSignCount,
	sum(case when SignupClass = 0 and state = 1 then 1 else 0 end) signCount,
	sum(case when SignupClass = 0 and state = 3 then 1 else 0 end) truancyCount,
	sum(case when SignupClass = 0 and state = 4 then 1 else 0 end) leaveCount,
    sum(case when SignupClass = 1 then 1 else 0 end) waitCount
from cu_TeacherCourse_Sign group by Tid
) t2 on t1.id = t2.Tid
where t1.LeagueId = {0} and TeachDate = '{1}' {2}", leagueId, date, cond);
            Dictionary<String, Object>[] teacherCourse = BasePage.bd.ExecuteDictArray(sql);
            foreach (Dictionary<String, Object> dr in teacherCourse)
                dr["keyCode"] = Fun.IDCode(Fun.cString(dr["id"]));
            data.Add("teacherCourse", teacherCourse);
            //签到情况
            sql = String.Format(@"select tid,t2.state,CustName,IsAuditions,SignupClass
from
cu_TeacherCourse t1
inner join cu_TeacherCourse_Sign t2 on t1.id = t2.tid
inner join cu_customers t3 on t2.cid = t3.id
where t1.LeagueId = {0} and TeachDate = '{1}' {2}
order by tid, t2.state", leagueId, date, cond);
            Dictionary<String, Object>[] sign = BasePage.bd.ExecuteDictArray(sql);
            data.Add("sign", sign);

            return BasePage.Json(ResultData.OK(data));
        }
        //将学员加入课程
        [ActionFilter(IsJson = true)]
        public JsonResult AddToCourse(String id, String CourseId, String keyCode, String cid, int signupClass)
        {

            var act = "";
            if (CourseId != "")
                act += " and datediff(m,(select BirdDate from cu_customers where id=" + cid + "),getdate())>=(select StartMonth from cu_Course where id =" + CourseId + ") and datediff(m,(select BirdDate from cu_customers where id=" + cid + "),getdate())<=(select EndMonth from cu_Course where id =" + CourseId + ")";

            if (!Fun.IDVerify(id, keyCode))
                return BasePage.Json(ResultData.Error(WebMessage.NoGrant));
            DataRow drUser = BasePage.GetUser(Session);
            String userId = Fun.cString(drUser["iuserid"]);
            String leagueId = Fun.cString(drUser["leagueId"]);
            //判断原数量
            String sql;
            if (signupClass == 0)
            {
                sql = String.Format(@"select studentNumber-isnull((select count(*) from cu_TeacherCourse_Sign where tid={0} and signupClass=0 and state!=4),0)
from cu_TeacherCourse t1 where id = {0}", id);
                if (BasePage.bd.ExecuteScalarInt(sql) == 0) return BasePage.Json(ResultData.Error("上课人数已满，不能再加入"));
            }
            else
            {
                sql = String.Format(@"select LineUpNumber-isnull((select count(*) from cu_TeacherCourse_Sign where tid={0} and signupClass=1),0)
from cu_TeacherCourse t1 where id = {0}", id);
                if (BasePage.bd.ExecuteScalarInt(sql) == 0) return BasePage.Json(ResultData.Error("排队人数已满，不能再加入"));
            }


            sql = String.Format(@"select count(*) from cu_customers  where id={0} and datediff(m,BirdDate,getdate())>=(select StartMonth from cu_Course where id ={1}) and datediff(m,BirdDate,getdate())<=(select EndMonth from cu_Course where id ={1})", cid, CourseId);
            if (BasePage.bd.ExecuteScalarInt(sql) == 0)
            {
                return BasePage.Json(ResultData.Error("加入失败"));
            }


            sql = String.Format(@"insert into cu_TeacherCourse_sign
(
	Cid,Tid,Pnumber,CreateId,Createdate,
    state,IsAuditions,SignupClass,LeagueId,ContractId,AchievementId,AchievementName,isMakeup
)
select 
	{0},{1},1,{3},getdate(),
	0,'R',{4},{2},t3.ContractId,t3.LatelyReceId,t4.sshowname,0
from
    cu_customers t3 
    left join tb_sys_users t4 on t3.LatelyReceId=t4.iuserid
where t3.id={0} and not exists(select * from cu_TeacherCourse_sign where cid={0} and tid={1})", cid, id, leagueId, userId, signupClass);
            bool t = BasePage.bd.ExecuteSQL(sql);
            return BasePage.Json(t ? ResultData.OK("加入成功") : ResultData.Error("加入失败"));
        }

        [ActionFilter(IsJson = true)]
        public JsonResult AddToCourseByStudent(String id, String keyCode, String tid, int signupClass)
        {
            if (!Fun.IDVerify(id, keyCode))
                return BasePage.Json(ResultData.Error(WebMessage.NoGrant));
            DataRow drUser = BasePage.GetUser(Session);
            String userId = Fun.cString(drUser["iuserid"]);
            String leagueId = Fun.cString(drUser["leagueId"]);
            //判断原数量
            String sql;
            if (signupClass == 0)
            {
                sql = String.Format(@"select studentNumber-isnull((select count(*) from cu_TeacherCourse_Sign where tid={0} and signupClass=0 and state!=4),0)
from cu_TeacherCourse t1 where id = {0}", tid);
                if (BasePage.bd.ExecuteScalarInt(sql) == 0) return BasePage.Json(ResultData.Error("上课人数已满，不能再加入"));
            }
            else
            {
                sql = String.Format(@"select LineUpNumber-isnull((select count(*) from cu_TeacherCourse_Sign where tid={0} and signupClass=1),0)
from cu_TeacherCourse t1 where id = {0}", tid);
                if (BasePage.bd.ExecuteScalarInt(sql) == 0) return BasePage.Json(ResultData.Error("排队人数已满，不能再加入"));
            }

            sql = String.Format(@"insert into cu_TeacherCourse_sign
(
	Cid,Tid,Pnumber,CreateId,Createdate,
    state,IsAuditions,SignupClass,LeagueId,ContractId,AchievementId,AchievementName,isMakeup
)
select 
	{0},{1},1,{3},getdate(),
	0,'R',{4},{2},t3.ContractId,t3.LatelyReceId,t4.sshowname,0
from
    cu_customers t3 
    left join tb_sys_users t4 on t3.LatelyReceId=t4.iuserid
where t3.id={0} and not exists(select * from cu_TeacherCourse_sign where cid={0} and tid={1})", id, tid, leagueId, userId, signupClass);
            bool t = BasePage.bd.ExecuteSQL(sql);
            return BasePage.Json(t ? ResultData.OK("加入成功") : ResultData.Error("加入失败"));
        }
        //退课
        [ActionFilter(IsJson = true)]
        public JsonResult AddToReturn(String id, String keyCode)
        {
            if (!Fun.IDVerify(id, keyCode))
                return BasePage.Json(ResultData.Error(WebMessage.NoGrant));
            String sql = String.Format(@"delete cu_TeacherCourse_sign where id={0} and state=0 ", id);
            bool t = BasePage.bd.ExecuteSQL(sql);
            return BasePage.Json(t ? ResultData.OK("退课成功") : ResultData.Error("退课失败(已上或请假)"));
        }
    }
}